import re

import xlrd
from xlutils.copy import copy
import sqlutils.dbhelper as db
from websites.crmeb import suishinet
from websites.crmeb.suishitype import getCartInfo
import random
from utils.time_util import current_time

fp = ""
rsheet = None
wb = None
ws = None
webDB = "crmeb"  # 网站数据库
xlspath = r"C:\Users\da\Desktop\1.xls"


def openExcel(filePath, sheet):
    """
    Excel打开操作
    filePath    文件路径
    sheet       表的索引
    """
    try:
        global rsheet, wb, ws, fp
        fp = filePath
        rb = xlrd.open_workbook(filePath)
        rb.sheets()
        rsheet = rb.sheet_by_index(sheet)
        wb = copy(rb)
        ws = wb.get_sheet(sheet)
    except Exception:
        return None, None, None
    return rsheet, wb, ws


def addUserToTable():
    """
    添加用户信息
    """
    pathImg = "https://xcsm.fun/uploads/attach/2022/06/20220603/82de79947dc3ef290c6fec477afa3bcb.jpg"

    with open("../file/email.txt", "rb") as f:
        accountArray = f.read().decode('utf8').split()
    with open("../file/username.txt", "rb") as f:  # 打开文件
        nameArray = f.read().decode('utf8').split("\n")

    if db.dbconnect() is not None:
        db.selectDataBase(webDB)

        index = 1
        a = min(len(accountArray), len(nameArray))
        for index in range(a):
            sql = r"INSERT INTO eb_user(account,pwd,real_name,nickname,avatar,add_time) VALUES ('" + accountArray[
                index] + "','123456','admin','" + nameArray[index] + "','" + pathImg + "','" + str(
                current_time()) + "')"
            db.executeSql(sql)
            print("已插入第" + str(index) + "个用户数据")
            index = index + 1
        return True
    else:
        return False


def addAppraiseToTable():
    """
    批量添加商品评论
    """
    if db.dbconnect() is not None:
        db.selectDataBase(webDB)

        with open("../file/pinglun.txt", "rb") as f:
            comment = f.read().decode('utf8').split()
        with open("../file/username.txt", "rb") as f:  # 打开文件
            nameArray = f.read().decode('utf8').split("\n")

        print("数据库连接成功")
        count = 0
        ##商品ID和销量
        storeInfo = db.select("eb_store_product", "select id,sales from eb_store_product")
        if storeInfo is not None and len(storeInfo) != 0:
            for item in range(len(storeInfo)):
                ##查商品属性
                attrInfo = db.select("eb_store_product_attr_value",
                                     r"select suk from eb_store_product_attr_value where product_id='" + str(
                                         storeInfo[item]['id']) + "'")
                ##平均每个属性的评论条数
                averageAppraise = int(int(storeInfo[item]['sales']) * 0.2 / len(attrInfo))

                for appraise in range(averageAppraise):
                    for attrIndex in range(len(attrInfo)):
                        sql = r"INSERT INTO eb_store_order_cart_info (product_id,oid,cart_info) VALUES ('" + str(
                            storeInfo[item]['id']) + "','" + str(count) + "','" + getCartInfo(
                            attrInfo[attrIndex]['suk']) + "')"
                        db.executeSql(sql)
                        db.executeSql(getAppraiseSql(item, storeInfo, count, comment, nameArray))
                        print("第" + str(count) + "条数据插入完成")
                        count = count + 1
        print("添加评论完成！")
        return True
    else:
        return False


def getAppraiseSql(item, storeInfo, count, comment, nameArray):
    """
    获取评论SQL
    """
    id = str(storeInfo[item]['id'])
    oid = str(count)
    productScore = str(random.randint(1, 5))
    serviceScore = str(random.randint(1, 5))
    commentStr = comment[random.randint(1, len(comment) - 10)]
    nameStr = nameArray[random.randint(1, len(nameArray) - 10)]

    ##2021-01-03 19:57:47至2022-6-3
    timeStr = str(random.randint(1609675067, 1654257467))
    userImg = "https://xcsm.fun/uploads/attach/2022/06/20220603/82de79947dc3ef290c6fec477afa3bcb.jpg"
    return r"INSERT INTO eb_store_product_reply (product_id,oid,product_score,service_score,comment,nickname,avatar,add_time) VALUES ('" + id + "','" + oid + "','" + productScore + "','" + serviceScore + "','" + commentStr + "','" + nameStr + "','" + userImg + "','" + timeStr + "')"


def insertArticle():
    """
    文章插入
    """

    connect = db.dbconnect()
    if connect is not None:
        db.selectDataBase(webDB)
        sqlValue()
        print("打开数据文件成功")

    else:
        print("数据库连接失败")
        return None


def sqlValue():
    """
    循环插入
    """

    try:
        index = 0
        imageUrlReg = r"src=['\"]([^'\"]+)[^>]*>"  # 图片地址

        valueStr = suishinet.getArtictlStr()
        if valueStr is not None and valueStr['success'] == 'true':
            title = str(valueStr['TITLE']) + "_碎食网"

            isHas = db.select("eb_article", r'select * from eb_article where title=' + title)
            if isHas is None:
                ##2021-01-03 19:57:47至2022-6-8
                timeStr = str(random.randint(1609675067, 1654657621))
                imgUrl = re.compile(imageUrlReg).findall(str(valueStr))[0]
                sql = r"INSERT INTO eb_article (cid,title,author,image_input,synopsis,status,add_time,is_hot,is_banner) VALUES ('5','" + title + "','碎食网','" + imgUrl + "','" + title + "','1','" + timeStr + "','0','0')"
                if db.executeSql(sql) is not None:
                    a = db.select("eb_article", "select max(id) from eb_article")[0]['max(id)']

                    if db.executeSql(
                            r"INSERT INTO eb_article_content (nid,content) VALUES ('" + str(a) + "','" + valueStr[
                                'BODY'] + "')",
                            "eb_article_content") is not None:
                        print("第" + str(index) + "条数据插入成功")
                else:
                    print("第" + str(index) + "条数据插入失败，Name=" + str(valueStr['TITLE']))
                index = index + 1
        sqlValue()
    except Exception:
        sqlValue()


def updateItemValue(row=0, column=0, value=None):
    """
    插入数据
    """
    global ws, fp
    ws.write(row, column, value)


def closeXlsWrite():
    global ws
    wb.save(fp)
